use std::path::{Path, PathBuf};

use indoc::indoc;
use rusqlite::{Connection, Row, named_params, params};

use super::PodcastDBId;

/// A struct representing a episode file (downloaded) in the database
#[derive(Debug, Clone)]
#[allow(dead_code)]
pub struct FileDB {
    pub id: PodcastDBId,
    pub episode_id: PodcastDBId,
    pub path: PathBuf,
}

impl FileDB {
    /// Try to convert a given row to a [`FileDB`] instance, using column names to resolve the values
    #[allow(dead_code)]
    pub fn try_from_row_named(row: &Row<'_>) -> Result<Self, rusqlite::Error> {
        // NOTE: all the names in "get" below are the *column names* as defined in migrations/001.sql#table_files (pseudo link)
        let path = PathBuf::from(row.get::<_, String>("path")?);
        Ok(Self {
            id: row.get("id")?,
            episode_id: row.get("episode_id")?,
            path,
        })
    }

    /// Try to convert a given row to a [`FileDB`] instance, using column names to resolve the values (with renamed id because of conflicts)
    pub fn try_from_row_named_alias_id(row: &Row<'_>) -> Result<Self, rusqlite::Error> {
        // NOTE: all the names in "get" below are the *column names* as defined in migrations/001.sql#table_files (pseudo link)
        let path = PathBuf::from(row.get::<_, String>("path")?);
        Ok(Self {
            id: row.get("fileid")?,
            episode_id: row.get("episode_id")?,
            path,
        })
    }
}

/// A struct representing a episode file (downloaded) in the database to be inserted
///
/// This is required as some fields are auto-generated by the database compared to [`FileDB`]
#[derive(Debug, Clone)]
pub struct FileDBInsertable<'a> {
    // generated by the database
    // pub id: PodcastDBId,
    pub episode_id: PodcastDBId,
    pub path: &'a Path,
}

impl<'a> FileDBInsertable<'a> {
    pub fn new(episode_id: PodcastDBId, path: &'a Path) -> Self {
        Self { episode_id, path }
    }

    /// Insert the current [`FileDBInsertable`] into the `files` table
    #[inline]
    pub fn insert_file(&self, con: &Connection) -> Result<usize, rusqlite::Error> {
        let mut stmt = con.prepare_cached(indoc! {"
            INSERT INTO files (episode_id, path)
            VALUES (:epid, :path);
        "})?;
        stmt.execute(named_params![
            ":epid": self.episode_id,
            ":path": self.path.to_string_lossy()
        ])
    }
}

/// Delete a file by episode id
///
/// This does *not* remove the actual (on disk) files!
pub fn delete_file(id: PodcastDBId, con: &Connection) -> Result<usize, rusqlite::Error> {
    let mut stmt = con.prepare_cached("DELETE FROM files WHERE episode_id = ?;")?;
    stmt.execute(params![id])
}

/// Delete multiple files by episode id
///
/// This does *not* remove the actual (on disk) files!
pub fn delete_files(ids: &[PodcastDBId], con: &Connection) -> Result<usize, rusqlite::Error> {
    // convert list of episode ids into a comma-separated String
    let episode_list: Vec<String> = ids.iter().map(std::string::ToString::to_string).collect();
    let episodes = episode_list.join(", ");

    let mut stmt = con.prepare_cached("DELETE FROM files WHERE episode_id = (?);")?;
    stmt.execute(params![episodes])
}
